#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from operator import itemgetter
import openpyxl
import csv
import re


def read_csv(file_path):
    with open(file_path, "r", encoding="gbk") as f:
        f_csv = csv.reader(f)
        headers = next(f_csv)
        csv_group = []
        for row in f_csv:
            csv_dict = dict(zip(headers, row))
            csv_group.append(csv_dict)
    data = list(sorted(csv_group, key=itemgetter("电信号码", "学校编号")))    # 将获取的数据根据字段进行排序

    idx, temp_dic = 0, {k: None for k in data[0]}    # 构造字典在列表中的索引，以及临时字典
    suffix = 1      # 针对重复表头在字典中的后缀
    for i, dic in enumerate(data):
        if dic["电信号码"] == temp_dic["电信号码"]:
            # 如果当前行与之前行的电信号码一致，则将当前行的数据合并到上一行并删除当前行
            source_dic = data[idx]
            source_dic["资费名称" + "_{}".format(suffix)] = dic["资费名称"]
            source_dic["资费生效日期" + "_{}".format(suffix)] = dic["资费生效日期"]
            source_dic["资费到期日期" + "_{}".format(suffix)] = dic["资费到期日期"]
            source_dic["单产品主套餐名称"] = source_dic["单产品主套餐名称"] or dic["单产品主套餐名称"]
            source_dic["融合主套餐名称"] = source_dic["融合主套餐名称"] or dic["融合主套餐名称"]
            data[i] = None      # 这一行被合并后将其赋值为None
            suffix += 1
        else:
            temp_dic = dic
            idx = i
            suffix = 1
    data = [x for x in data if x is not None]       # 过滤掉所有为None的行
    return data


def make_xlsx(data, file_path):
    # 创建excel
    wb = openpyxl.Workbook()
    wb.create_sheet(title="测试", index=0)
    ws = wb["测试"]

    row, final_title = 2, []
    for dic in data:
        title = list(dic.keys())
        if len(title) > len(final_title):
            final_title = title
        values = dic.values()
        for i, value in enumerate(values):
            ws.cell(row, i + 1).value = value
        row += 1

    # 将表头的所有_x后缀全部移除
    final_title = [re.sub("_[0-9]+", "", x) for x in final_title]
    for j, tle in enumerate(final_title):
        ws.cell(1, j + 1).value = tle

    wb.save(file_path)


if __name__ == "__main__":
    csv_data = read_csv("./测试.csv")
    make_xlsx(csv_data, "./测试.xlsx")
